


/*
	this is procedure creation
*/

ALTER   proc Customer_Cancel_Proc
	@Lcl_LineItem_ID int,
	@Lcl_CCOL_Reason	 varchar(50),
	@Lcl_CCOL_Cancel_By_ID	 varchar(10),
	@Lcl_CCOL_Cancel_By_Name	 varchar(50)
	
	 
AS 
	SET NOCOUNT ON
/*
        declaring local variables
*/
	
	declare @Lcl_SalesOrder_ID int
	declare @Lcl_CCOL_Line_Item_No int
	declare @Lcl_Product_ID	int
	declare @Lcl_CCOL_Qty	int
	declare @Lcl_CCOL_Unit_Cost	float
	declare @Lcl_CCOL_Amount	float
	declare @Lcl_CCOL_Requested_Date	datetime
	declare @Lcl_CCOL_Booking_Date	datetime
	declare @LCl_CCOL_Sugg_Schedule_Date_With_Substitutables	datetime
	declare @Lcl_CCOL_Sugg_Schedule_Date_Without_Substitutables	datetime
	declare @Lcl_CCOL_Schedule_Date	datetime
	declare @Lcl_CCOL_Revised_Date	datetime

/*
	declaring CURSOR
*/

declare Customer_Select_Cancel_Cursor cursor for

select 	SalesOrder_ID,	
	COOL_Line_Item_No,
	Product_ID,
	COOL_Qty,
	COOL_Unit_Cost,
	COOL_Amount,
	COOL_Requested_Date,
	COOL_Booking_Date,
	COOL_Sugg_Schedule_Date_With_Substitutables,
	COOL_Sugg_Schedule_Date_Without_Substitutables,
	COOL_Schedule_Date,
	COOL_Revised_Date
from 	Customer_Open_Order_LineItem 
where 	LineItem_ID = @Lcl_LineItem_ID

/*
	opening the CURSOR
*/

OPEN 	Customer_Select_Cancel_Cursor

/*
	Fetching the rows from cursot into the local variables
*/
	
FETCH NEXT FROM Customer_Select_Cancel_Cursor 
INTO	@Lcl_SalesOrder_ID,
	@Lcl_CCOL_Line_Item_No, 
	@Lcl_Product_ID, 
	@Lcl_CCOL_Qty, 
	@Lcl_CCOL_Unit_Cost, 
	@Lcl_CCOL_Amount, 
	@Lcl_CCOL_Requested_Date,
	@Lcl_CCOL_Booking_Date, 
	@LCl_CCOL_Sugg_Schedule_Date_With_Substitutables,
	@Lcl_CCOL_Sugg_Schedule_Date_Without_Substitutables, 
	@Lcl_CCOL_Schedule_Date, 
	@Lcl_CCOL_Revised_Date					


/*
	here the values in the local variables are inserted into the table
*/

BEGIN
INSERT INTO Customer_Cancel_Order_LineItem
      ( LineItem_ID,
	SalesOrder_ID,
	CCOL_Line_Item_No, 
	Product_ID,
	CCOL_Qty ,
	CCOL_Unit_Cost,
	CCOL_Amount,
	CCOL_Requested_Date,
	CCOL_Booking_Date,
	CCOL_Sugg_Schedule_Date_With_Substitutables,
	CCOL_Sugg_Schedule_Date_Without_Substitutables,
	CCOL_Schedule_Date,
	CCOL_Revised_Date,
	CCOL_Reason, 
	CCOL_Cancel_By_ID, 
	CCOL_Cancel_By_Name ) 

values
       ( @Lcl_LineItem_ID,
	 @Lcl_SalesOrder_ID, 
	 @Lcl_CCOL_Line_Item_No ,
	 @Lcl_Product_ID	,
	 @Lcl_CCOL_Qty	,
	 @Lcl_CCOL_Unit_Cost	,
	 @Lcl_CCOL_Amount	,
	 @Lcl_CCOL_Requested_Date	,
	 @Lcl_CCOL_Booking_Date	,
	 @LCl_CCOL_Sugg_Schedule_Date_With_Substitutables	,
	 @Lcl_CCOL_Sugg_Schedule_Date_Without_Substitutables	,
	 @Lcl_CCOL_Schedule_Date	,
	 @Lcl_CCOL_Revised_Date	,
	 @Lcl_CCOL_Reason ,
	 @Lcl_CCOL_Cancel_By_ID ,
	 @Lcl_CCOL_Cancel_By_Name )	

/*
	after insertion the delete operation is done to the other table
*/
DELETE FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @Lcl_LineItem_ID
UPDATE Sales_Order SET SO_Is_Cancel = 0 where SalesOrder_ID = @Lcl_SalesOrder_ID

/*
	here closing the cursor and deaalocating the cursor
*/

CLOSE Customer_Select_Cancel_Cursor
DEALLOCATE Customer_Select_Cancel_Cursor

END




